{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data source\n",
    "https://apps.who.int/gho/data/node.main.A1363?lang=en"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Estimated data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2017</td>\n",
       "      <td>630 308 [495 000 - 801 000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2017</td>\n",
       "      <td>4 615 605 [3 106 000 - 6 661 000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year                       No. of cases\n",
       "0  Afghanistan   2017        630 308 [495 000 - 801 000]\n",
       "1      Algeria   2017                                  0\n",
       "2       Angola   2017  4 615 605 [3 106 000 - 6 661 000]\n",
       "3    Argentina   2017                                  0\n",
       "4      Armenia   2017                                  0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "est_cases = pd.read_csv('estimated_cases.csv', skiprows=1)\n",
    "est_cases = est_cases.melt(id_vars='Country', var_name='Year', value_name='No. of cases')\n",
    "est_cases.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2017</td>\n",
       "      <td>298 [110 - 510]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2017</td>\n",
       "      <td>13 316 [9970 - 16600]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year          No. of deaths\n",
       "0  Afghanistan   2017        298 [110 - 510]\n",
       "1      Algeria   2017                      0\n",
       "2       Angola   2017  13 316 [9970 - 16600]\n",
       "3    Argentina   2017                      0\n",
       "4      Armenia   2017                      0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "est_deaths = pd.read_csv('estimated_deaths.csv', skiprows=1)\n",
    "est_deaths = est_deaths.melt(id_vars='Country', var_name='Year', value_name='No. of deaths')\n",
    "est_deaths.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of cases</th>\n",
       "      <th>No. of deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2017</td>\n",
       "      <td>630 308 [495 000 - 801 000]</td>\n",
       "      <td>298 [110 - 510]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2017</td>\n",
       "      <td>4 615 605 [3 106 000 - 6 661 000]</td>\n",
       "      <td>13 316 [9970 - 16600]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year                       No. of cases  \\\n",
       "0  Afghanistan   2017        630 308 [495 000 - 801 000]   \n",
       "1      Algeria   2017                                  0   \n",
       "2       Angola   2017  4 615 605 [3 106 000 - 6 661 000]   \n",
       "3    Argentina   2017                                  0   \n",
       "4      Armenia   2017                                  0   \n",
       "\n",
       "           No. of deaths  \n",
       "0        298 [110 - 510]  \n",
       "1                      0  \n",
       "2  13 316 [9970 - 16600]  \n",
       "3                      0  \n",
       "4                      0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "est_no = pd.merge(est_cases, est_deaths)\n",
    "est_no.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of cases</th>\n",
       "      <th>No. of deaths</th>\n",
       "      <th>No. of cases_median</th>\n",
       "      <th>No. of cases_min</th>\n",
       "      <th>No. of cases_max</th>\n",
       "      <th>No. of deaths_median</th>\n",
       "      <th>No. of deaths_min</th>\n",
       "      <th>No. of deaths_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2017</td>\n",
       "      <td>630308[495000-801000]</td>\n",
       "      <td>298[110-510]</td>\n",
       "      <td>630308</td>\n",
       "      <td>495000</td>\n",
       "      <td>801000</td>\n",
       "      <td>298</td>\n",
       "      <td>110</td>\n",
       "      <td>510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2017</td>\n",
       "      <td>4615605[3106000-6661000]</td>\n",
       "      <td>13316[9970-16600]</td>\n",
       "      <td>4615605</td>\n",
       "      <td>3106000</td>\n",
       "      <td>6661000</td>\n",
       "      <td>13316</td>\n",
       "      <td>9970</td>\n",
       "      <td>16600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year              No. of cases      No. of deaths  \\\n",
       "0  Afghanistan   2017     630308[495000-801000]       298[110-510]   \n",
       "1      Algeria   2017                         0                  0   \n",
       "2       Angola   2017  4615605[3106000-6661000]  13316[9970-16600]   \n",
       "3    Argentina   2017                         0                  0   \n",
       "4      Armenia   2017                         0                  0   \n",
       "\n",
       "  No. of cases_median No. of cases_min No. of cases_max No. of deaths_median  \\\n",
       "0              630308           495000           801000                  298   \n",
       "1                   0              NaN              NaN                    0   \n",
       "2             4615605          3106000          6661000                13316   \n",
       "3                   0              NaN              NaN                    0   \n",
       "4                   0              NaN              NaN                    0   \n",
       "\n",
       "  No. of deaths_min No. of deaths_max  \n",
       "0               110               510  \n",
       "1               NaN               NaN  \n",
       "2              9970             16600  \n",
       "3               NaN               NaN  \n",
       "4               NaN               NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for col in ['No. of cases', 'No. of deaths']:\n",
    "    est_no[col] = est_no[col].str.replace(' ', '')\n",
    "    est_no[col+'_median'] = est_no[col].str.extract('([0-9.]+)')\n",
    "    est_no[col+'_min'] = est_no[col].str.extract('[0-9.]+\\[([0-9.]+)')\n",
    "    est_no[col+'_max'] = est_no[col].str.extract('[0-9.]+\\[[0-9.]+\\-([0-9.]+)')\n",
    "    \n",
    "est_no.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "who_region = {}\n",
    "\n",
    "# African Region AFRO\n",
    "afro = \"Algeria, Angola, Cabo Verde, Eswatini, Sao Tome and Principe, Benin, South Sudan, Western Sahara, Congo (Brazzaville), Congo (Kinshasa), Cote d'Ivoire, Botswana, Burkina Faso, Burundi, Cameroon, Cape Verde, Central African Republic, Chad, Comoros, Ivory Coast, Côte d'Ivoire, Democratic Republic of the Congo, Equatorial Guinea, Eritrea, Ethiopia, Gabon, Gambia, Ghana, Guinea, Guinea-Bissau, Kenya, Lesotho, Liberia, Madagascar, Malawi, Mali, Mauritania, Mauritius, Mozambique, Namibia, Niger, Nigeria, Republic of the Congo, Rwanda, São Tomé and Príncipe, Senegal, Seychelles, Sierra Leone, Somalia, South Africa, Swaziland, Togo, Uganda, Tanzania, United Republic of Tanzania, Zambia, Zimbabwe\"\n",
    "afro = [i.strip() for i in afro.split(',')]\n",
    "for i in afro:\n",
    "    who_region[i] = 'Africa'\n",
    "    \n",
    "# Region of the Americas PAHO\n",
    "paho = 'Antigua and Barbuda, Argentina, Bahamas, Barbados, Belize, Bolivia, Bolivia (Plurinational State of), Brazil, Canada, Chile, Colombia, Congo, Costa Rica, Cuba, Dominica, Dominican Republic, Ecuador, El Salvador, Grenada, Guatemala, Guyana, Haiti, Honduras, Jamaica, Mexico, Nicaragua, Panama, Paraguay, Peru, Saint Kitts and Nevis, Saint Lucia, Saint Vincent and the Grenadines, Suriname, Trinidad and Tobago, United States, US, United States of America, Uruguay, Venezuela, Venezuela (Bolivarian Republic of)'\n",
    "paho = [i.strip() for i in paho.split(',')]\n",
    "for i in paho:\n",
    "    who_region[i] = 'Americas'\n",
    "\n",
    "# South-East Asia Region SEARO\n",
    "searo = 'Bangladesh, Bhutan, India, Indonesia, Maldives, Myanmar, Burma, Nepal, Sri Lanka, Thailand, Timor-Leste'\n",
    "searo = [i.strip() for i in searo.split(',')]\n",
    "for i in searo:\n",
    "    who_region[i] = 'South-East Asia'\n",
    "\n",
    "# European Region EURO\n",
    "euro = 'Albania, Andorra, Greenland, Kosovo, Holy See, Liechtenstein, Armenia, Czechia, Austria, Azerbaijan, Belarus, Belgium, Bosnia and Herzegovina, Bulgaria, Croatia, Cyprus, Czech Republic, Denmark, Estonia, Finland, France, Georgia, Germany, Greece, Hungary, Iceland, Ireland, Israel, Italy, Kazakhstan, Kyrgyzstan, Latvia, Lithuania, Luxembourg, Malta, Monaco, Montenegro, Netherlands, North Macedonia, Republic of North Macedonia, Norway, Poland, Portugal, Moldova, Republic of Moldova, Romania, Russia, Russian Federation, San Marino, Serbia, Slovakia, Slovenia, Spain, Sweden, Switzerland, Tajikistan, Turkey, Turkmenistan, Ukraine, United Kingdom, United Kingdom of Great Britain and Northern Ireland, Uzbekistan'\n",
    "euro = [i.strip() for i in euro.split(',')]\n",
    "for i in euro:\n",
    "    who_region[i] = 'Europe'\n",
    "\n",
    "# Eastern Mediterranean Region EMRO\n",
    "emro = 'Afghanistan, Bahrain, Djibouti, Egypt, Iran, Iran (Islamic Republic of), Iraq, Jordan, Kuwait, Lebanon, Libya, Morocco, Oman, Pakistan, Palestine, West Bank and Gaza, Qatar, Saudi Arabia, Somalia, Sudan, Syria, Syrian Arab Republic, Tunisia, United Arab Emirates, Yemen'\n",
    "emro = [i.strip() for i in emro.split(',')]\n",
    "for i in emro:\n",
    "    who_region[i] = 'Eastern Mediterranean'\n",
    "\n",
    "# Western Pacific Region WPRO\n",
    "wpro = \"Australia, Brunei, Brunei Darussalam, Republic of Korea, Cambodia, China, Cook Islands, Fiji, Japan, Kiribati, Laos, Lao People's Democratic Republic, Malaysia, Marshall Islands, Micronesia, Mongolia, Nauru, North Korea, New Zealand, Niue, Palau, Papua New Guinea, Philippines, South Korea, Democratic People's Republic of Korea, Samoa, Singapore, Solomon Islands, Taiwan, Taiwan*, Tonga, Tuvalu, Vanuatu, Vietnam, Viet Nam\"\n",
    "wpro = [i.strip() for i in wpro.split(',')]\n",
    "for i in wpro:\n",
    "    who_region[i] = 'Western Pacific'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of cases</th>\n",
       "      <th>No. of deaths</th>\n",
       "      <th>No. of cases_median</th>\n",
       "      <th>No. of cases_min</th>\n",
       "      <th>No. of cases_max</th>\n",
       "      <th>No. of deaths_median</th>\n",
       "      <th>No. of deaths_min</th>\n",
       "      <th>No. of deaths_max</th>\n",
       "      <th>WHO Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2017</td>\n",
       "      <td>630308[495000-801000]</td>\n",
       "      <td>298[110-510]</td>\n",
       "      <td>630308</td>\n",
       "      <td>495000</td>\n",
       "      <td>801000</td>\n",
       "      <td>298</td>\n",
       "      <td>110</td>\n",
       "      <td>510</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2017</td>\n",
       "      <td>4615605[3106000-6661000]</td>\n",
       "      <td>13316[9970-16600]</td>\n",
       "      <td>4615605</td>\n",
       "      <td>3106000</td>\n",
       "      <td>6661000</td>\n",
       "      <td>13316</td>\n",
       "      <td>9970</td>\n",
       "      <td>16600</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Americas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year              No. of cases      No. of deaths  \\\n",
       "0  Afghanistan   2017     630308[495000-801000]       298[110-510]   \n",
       "1      Algeria   2017                         0                  0   \n",
       "2       Angola   2017  4615605[3106000-6661000]  13316[9970-16600]   \n",
       "3    Argentina   2017                         0                  0   \n",
       "4      Armenia   2017                         0                  0   \n",
       "\n",
       "  No. of cases_median No. of cases_min No. of cases_max No. of deaths_median  \\\n",
       "0              630308           495000           801000                  298   \n",
       "1                   0              NaN              NaN                    0   \n",
       "2             4615605          3106000          6661000                13316   \n",
       "3                   0              NaN              NaN                    0   \n",
       "4                   0              NaN              NaN                    0   \n",
       "\n",
       "  No. of deaths_min No. of deaths_max             WHO Region  \n",
       "0               110               510  Eastern Mediterranean  \n",
       "1               NaN               NaN                 Africa  \n",
       "2              9970             16600                 Africa  \n",
       "3               NaN               NaN               Americas  \n",
       "4               NaN               NaN                 Europe  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "est_no['WHO Region'] = est_no['Country'].map(who_region)\n",
    "est_no.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "est_no[est_no['WHO Region'].isna()]['Country'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "est_no.to_csv('estimated_numbers.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reported data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2017</td>\n",
       "      <td>161778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2017</td>\n",
       "      <td>3874892.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year  No. of cases\n",
       "0  Afghanistan   2017      161778.0\n",
       "1      Algeria   2017           0.0\n",
       "2       Angola   2017     3874892.0\n",
       "3    Argentina   2017           0.0\n",
       "4      Armenia   2017           0.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rep_cases = pd.read_csv('reported_indigenous_confirmed_cases.csv', skiprows=1)\n",
    "rep_cases = rep_cases.melt(id_vars='Country', var_name='Year', value_name='No. of cases')\n",
    "rep_cases.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2017</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2017</td>\n",
       "      <td>13967.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year  No. of deaths\n",
       "0  Afghanistan   2017           10.0\n",
       "1      Algeria   2017            0.0\n",
       "2       Angola   2017        13967.0\n",
       "3    Argentina   2017            1.0\n",
       "4      Armenia   2017            NaN"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rep_deaths = pd.read_csv('reported_deaths.csv', skiprows=1)\n",
    "rep_deaths = rep_deaths.melt(id_vars='Country', var_name='Year', value_name='No. of deaths')\n",
    "rep_deaths.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of cases</th>\n",
       "      <th>No. of deaths</th>\n",
       "      <th>WHO Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2017</td>\n",
       "      <td>161778.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2017</td>\n",
       "      <td>3874892.0</td>\n",
       "      <td>13967.0</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Americas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year  No. of cases  No. of deaths             WHO Region\n",
       "0  Afghanistan   2017      161778.0           10.0  Eastern Mediterranean\n",
       "1      Algeria   2017           0.0            0.0                 Africa\n",
       "2       Angola   2017     3874892.0        13967.0                 Africa\n",
       "3    Argentina   2017           0.0            1.0               Americas\n",
       "4      Armenia   2017           0.0            NaN                 Europe"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rep_no = pd.merge(rep_cases, rep_deaths, how='outer')\n",
    "rep_no['WHO Region'] = rep_no['Country'].map(who_region)\n",
    "rep_no.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rep_no[rep_no['WHO Region'].isna()]['Country'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "rep_no.to_csv('reported_numbers.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Incidence_per_1000_population_at_risk.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>No. of cases</th>\n",
       "      <th>WHO Region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2018</td>\n",
       "      <td>29.01</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2018</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2018</td>\n",
       "      <td>228.91</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2018</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Americas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2018</td>\n",
       "      <td>0.00</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country   Year  No. of cases             WHO Region\n",
       "0  Afghanistan   2018         29.01  Eastern Mediterranean\n",
       "1      Algeria   2018          0.00                 Africa\n",
       "2       Angola   2018        228.91                 Africa\n",
       "3    Argentina   2018          0.00               Americas\n",
       "4      Armenia   2018          0.00                 Europe"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inc = pd.read_csv('incidence_per_1000_population_at_risk.csv', skiprows=1)\n",
    "inc = inc.melt(id_vars='Country', var_name='Year', value_name='No. of cases')\n",
    "inc['WHO Region'] = inc['Country'].map(who_region)\n",
    "inc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([], dtype=object)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inc[inc['WHO Region'].isna()]['Country'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "inc.to_csv('incidence_per_1000_pop_at_risk.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
